#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--稽核上层表数据量
select 1/count(*) from hive.edu_dwb.ydh_dwb_check_detail_wide;



delete from hive.edu_dws.ydh_dws_check_wide_timecnt where 1=1;
insert into hive.edu_dws.ydh_dws_check_wide_timecnt
with tmp1 as (
    select create_date,           -- '创建时间'
           class_date,
           class_id,              -- '班级id'
           student_id,            -- '学员id'
           date_parse(substr(signin_time,1,19),'%Y-%m-%d %H:%i:%s') as signin_time,            --实际打卡时间
           signin_date,           -- '签到日期'
           date_parse(substr(begin_time,1,19),'%Y-%m-%d %H:%i:%s') as begin_time,              --请假开始时间
           begin_time_type,       -- '1：上午 2：下午'
           date_parse(substr(end_time,1,19),'%Y-%m-%d %H:%i:%s') as end_time,                  --请假结束时间
           end_time_type,         -- '1：上午 2：下午'
           days,                  -- '请假/已休天数'
           morning_template_id,   -- '上午出勤模板id'
           morning_begin_time,    -- '上午开始时间'
           morning_end_time,      -- '上午结束时间'
           afternoon_template_id, -- '下午出勤模板id'
           afternoon_begin_time,  -- '下午开始时间'
           afternoon_end_time,    -- '下午结束时间'
           evening_template_id,   -- '晚上出勤模板id'
           evening_begin_time,    -- '晚上开始时间'
           evening_end_time,      -- '晚上结束时间'
           date_parse(use_begin_date,'%Y-%m-%d') as use_begin_date,        -- '模板使用开始日期'
           date_parse(use_end_date,'%Y-%m-%d') as use_end_date,         -- '模板使用结束日期'
           --打卡
           date_add('minute',-40,date_parse(concat(signin_date,' ',morning_begin_time),'%Y-%m-%d %H:%i:%s')) as morning_begin,      --上午最早打卡时间
           date_add('minute',10,date_parse(concat(signin_date,' ',morning_begin_time),'%Y-%m-%d %H:%i:%s')) as morning_begin_last,  --上午最晚打卡时间
           date_parse(concat(signin_date,' ',morning_end_time),'%Y-%m-%d %H:%i:%s') as morning_end,                                 --上午下课时间
           date_add('minute',-40,date_parse(concat(signin_date,' ',afternoon_begin_time),'%Y-%m-%d %H:%i:%s')) as afternoon_begin,      --下午最早打卡时间
           date_add('minute',10,date_parse(concat(signin_date,' ',afternoon_begin_time),'%Y-%m-%d %H:%i:%s')) as afternoon_begin_last,  --下午最晚打卡时间
           date_parse(concat(signin_date,' ',afternoon_end_time),'%Y-%m-%d %H:%i:%s') as afternoon_end,                                 --下午下课时间
           date_add('minute',-40,date_parse(concat(signin_date,' ',evening_begin_time),'%Y-%m-%d %H:%i:%s')) as evening_begin,      --晚上最早打卡时间
           date_add('minute',10,date_parse(concat(signin_date,' ',evening_begin_time),'%Y-%m-%d %H:%i:%s')) as evening_begin_last,  --晚上最晚打卡时间
           date_parse(concat(signin_date,' ',evening_end_time),'%Y-%m-%d %H:%i:%s') as evening_end                                --晚上下课时间
    from hive.edu_dwb.ydh_dwb_check_detail_wide
),
tmp2 as (
    select create_date,           -- '创建时间'
           class_date,
           class_id,              -- '班级id'
           student_id,            -- '学员id'
           signin_time,            --实际打卡时间
           signin_date,           -- '签到日期'
           begin_time,              --请假开始时间
           begin_time_type,       -- '1：上午 2：下午'
           end_time,                  --请假结束时间
           end_time_type,         -- '1：上午 2：下午'
           days,                  -- '请假/已休天数'
           morning_template_id,   -- '上午出勤模板id'
           morning_begin_time,    -- '上午开始时间'
           morning_end_time,      -- '上午结束时间'
           afternoon_template_id, -- '下午出勤模板id'
           afternoon_begin_time,  -- '下午开始时间'
           afternoon_end_time,    -- '下午结束时间'
           evening_template_id,   -- '晚上出勤模板id'
           evening_begin_time,    -- '晚上开始时间'
           evening_end_time,      -- '晚上结束时间'
           use_begin_date,        -- '模板使用开始日期'
           use_end_date,         -- '模板使用结束日期'
           --打卡
           morning_begin,      --上午最早打卡时间
           morning_begin_last,  --上午最晚打卡时间
           morning_end,          --上午下课时间
           afternoon_begin,      --下午最早打卡时间
           afternoon_begin_last,  --下午最晚打卡时间
           afternoon_end,         --下午下课时间
           evening_begin,      --晚上最早打卡时间
           evening_begin_last,  --晚上最晚打卡时间
           evening_end,           --晚上下课时间
           case when signin_time between morning_begin and morning_end then 'am'        --上午课程时间内打卡
                when signin_time between afternoon_begin and afternoon_end then 'pm'    --下午课程时间内打卡
                when signin_time between evening_begin and evening_end then 'nt'       --晚上课程时间内打卡
                else 'invalid'                                                                  --无效打卡
                end as signin_flg ,                                                              --打卡标记
           case when begin_time is not null then 'leave' else 'other' end as leave_type    --请假数据标记
    from tmp1
),
tmp3 as (
    select create_date,           -- '创建时间'
           class_date,
           class_id,              -- '班级id'
           student_id,            -- '学员id'
           signin_time,            --实际打卡时间
           signin_date,           -- '签到日期'
           begin_time,              --请假开始时间
           begin_time_type,       -- '1：上午 2：下午'
           end_time,                  --请假结束时间
           end_time_type,         -- '1：上午 2：下午'
           days,                  -- '请假/已休天数'
           morning_template_id,   -- '上午出勤模板id'
           morning_begin_time,    -- '上午开始时间'
           morning_end_time,      -- '上午结束时间'
           afternoon_template_id, -- '下午出勤模板id'
           afternoon_begin_time,  -- '下午开始时间'
           afternoon_end_time,    -- '下午结束时间'
           evening_template_id,   -- '晚上出勤模板id'
           evening_begin_time,    -- '晚上开始时间'
           evening_end_time,      -- '晚上结束时间'
           use_begin_date,        -- '模板使用开始日期'
           use_end_date,         -- '模板使用结束日期'
           --打卡
           morning_begin,      --上午最早打卡时间
           morning_begin_last,  --上午最晚打卡时间
           morning_end,          --上午下课时间
           afternoon_begin,      --下午最早打卡时间
           afternoon_begin_last,  --下午最晚打卡时间
           afternoon_end,         --下午下课时间
           evening_begin,      --晚上最早打卡时间
           evening_begin_last,  --晚上最晚打卡时间
           evening_end,           --晚上下课时间
           signin_flg,
           leave_type,
           row_number() over(partition by class_id,student_id,signin_date,signin_flg order by signin_time)  as rn,
           row_number() over(partition by class_id,student_id,begin_time,end_time,class_date)  as rn2
    from tmp2
),
tmp4 as (
    select
        --签到时间 在上课前40分钟~上课时间点10分钟之内，为正常打卡   在上课后10分钟~放学时间之内，为迟到
        create_date,           -- '创建时间'
        class_date,
        class_id,              -- '班级id'
        student_id,            -- '学员id'
        signin_time,           --实际打卡时间
        signin_date,           -- '签到日期'
        begin_time,            --请假开始时间
        begin_time_type,       -- '1：上午 2：下午'
        end_time,              --请假结束时间
        end_time_type,         -- '1：上午 2：下午'
        days,                  -- '请假/已休天数'
        morning_template_id,   -- '上午出勤模板id'
        morning_begin_time,    -- '上午开始时间'
        morning_end_time,      -- '上午结束时间'
        afternoon_template_id, -- '下午出勤模板id'
        afternoon_begin_time,  -- '下午开始时间'
        afternoon_end_time,    -- '下午结束时间'
        evening_template_id,   -- '晚上出勤模板id'
        evening_begin_time,    -- '晚上开始时间'
        evening_end_time,      -- '晚上结束时间'
        use_begin_date,        -- '模板使用开始日期'
        use_end_date,          -- '模板使用结束日期'
        --打卡
        morning_begin,         --上午最早打卡时间
        morning_begin_last,    --上午最晚打卡时间
        morning_end,           --上午下课时间
        afternoon_begin,       --下午最早打卡时间
        afternoon_begin_last,  --下午最晚打卡时间
        afternoon_end,         --下午下课时间
        evening_begin,         --晚上最早打卡时间
        evening_begin_last,    --晚上最晚打卡时间
        evening_end,           --晚上下课时间
        signin_flg,
        leave_type,
        rn2,
        case when signin_time between morning_begin and morning_begin_last and rn = 1
                 then 'mon_normal' --上午正常出勤
             when signin_time between afternoon_begin and afternoon_begin_last  and rn = 1
                 then 'aft_normal' --下午正常出勤
             when signin_time between evening_begin and evening_begin_last and rn = 1
                 then 'even_normal' --晚上正常出勤
             when signin_time between morning_begin_last and morning_end and rn = 1
                 then 'mon_late' --上午迟到
             when signin_time between afternoon_begin_last and afternoon_end and rn = 1
                 then 'aft_late' --下午迟到
             when signin_time between evening_begin_last and evening_end and rn = 1
                 then 'even_late' --晚上迟到
             else 'other'
            end as signin_type
    from tmp3
)
select create_date,
       class_id,
       signin_flg as time_type,
       signin_date as dt,
       count( case when signin_type in ('mon_normal','aft_normal','even_normal') then student_id else null end) as studying_cnt,
       count( case when signin_type in ('mon_late','aft_late','even_late') then student_id else null end) as late_cnt,
       0 as leave_cnt
from tmp4
where signin_flg <>'invalid'
group by
       create_date,
       class_id,
       signin_flg ,
       signin_date
--order by class_id,signin_date,time_type
union all
select create_date,
       class_id,
       case when begin_time_type =1 then 'am'
            when begin_time_type =2 then 'pm'
            end as time_type,
       substr(cast(begin_time as varchar),1,10) as dt,
       0 as studying_cnt,
       0 as late_cnt,
       count(student_id) as leave_cnt
from tmp4
where leave_type='leave' and rn2=1
group by
       create_date,
       class_id,
       case when begin_time_type =1 then 'am'
            when begin_time_type =2 then 'pm'
            end ,
       substr(cast(begin_time as varchar),1,10)
;

"
a=$?
if [ ${a} -eq 0 ];then 
	echo 'Program running status return code:' ${a}
	echo '=============================打完收工！================================='
else 
	echo 'Program running status return code:' ${a}
	echo '=============================程序出BUG咯！=============================='
	fi